package com.dao.impl;

import com.dao.UserLoginDao;
import com.dao.UserManagerDao;
import com.pojo.Users;
import com.util.JDBCUtil;
import jdk.nashorn.internal.scripts.JD;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/*
* 用户管理持久层
* */
public class UserManagerDaoImpl implements UserManagerDao {
    @Override
    public void insertUser(Users users) {
        Connection connectionn=null;
        PreparedStatement preparedStatement=null;
        try {
            connectionn= JDBCUtil.getConnection();
            connectionn.setAutoCommit(false);
            preparedStatement= connectionn.prepareStatement("insert into users values (default,?,?,?,?,?)");
            //匹配参数
            preparedStatement.setString(1, users.getUsername());
            preparedStatement.setString(2,users.getUserpwd());
            preparedStatement.setString(3, users.getUsersex());
            preparedStatement.setString(4, users.getPhonenumber());
            preparedStatement.setString(5, users.getQqnumber());
            preparedStatement.execute();
            connectionn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollbackConnection(connectionn);
        }finally {
            JDBCUtil.closeConnection(connectionn);
        }
    }

    /*
    * 查询用户
    * */
    @Override
    public List<Users> selectUser(Users users) {
        Connection connection=null;
        List<Users> usersList=new ArrayList<>();
        PreparedStatement preparedStatement=null;
        try {
            connection=JDBCUtil.getConnection();
            //具体的sql是什么，需要用户传递
            String sql=this.creatSQL(users);
            preparedStatement=connection.prepareStatement(sql);
            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Users users1=new Users();
                users1.setUserid(resultSet.getInt("userid"));
                users1.setUsername(resultSet.getString("username"));
                users1.setUsersex(resultSet.getString("usersex"));
                users1.setPhonenumber(resultSet.getString("phonenumber"));
                users1.setQqnumber(resultSet.getString("qqnumber"));
                usersList.add(users1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection);
        }
        return usersList;
    }


    /*
    * 根据用户id查询用户
    * */
    @Override
    public Users selectUserByUserId(int userid) {
        Connection connection=null;
        Users users=null;
        try {
            connection=JDBCUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement("select * from users where userid=?");
            preparedStatement.setInt(1,userid);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                users=new Users();
                users.setUserid(resultSet.getInt("userid"));
                users.setUsername(resultSet.getString("username"));
                users.setUsersex(resultSet.getString("usersex"));
                users.setPhonenumber(resultSet.getString("phonenumber"));
                users.setQqnumber(resultSet.getString("qqnumber"));
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.closeConnection(connection);
        }
        return users;
    }


    /*
    * 更新用户
    * */
    @Override
    public void updateUserByUserId(Users users) {
        Connection connection=null;
        try {
            connection= JDBCUtil.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement= connection.prepareStatement("update users set username=?,usersex=?,phonenumber=?,qqnumber=? where userid=?");
            preparedStatement.setString(1,users.getUsername());
            preparedStatement.setString(2,users.getUsersex());
            preparedStatement.setString(3,users.getPhonenumber());
            preparedStatement.setString(4,users.getQqnumber());
            preparedStatement.setInt(5,users.getUserid());
            preparedStatement.execute();
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollbackConnection(connection);
        }finally {
            JDBCUtil.closeConnection(connection);
        }
    }

    /*
    * 根据用户id删除用户
    * */
    @Override
    public void deleteUserById(int userid) {
        Connection connection=null;
        try {
            connection= JDBCUtil.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement=connection.prepareStatement("delete from users where userid=?");
            preparedStatement.setInt(1,userid);
            preparedStatement.execute();
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollbackConnection(connection);
        }finally {
            JDBCUtil.closeConnection(connection);
        }
    }

    //拼接查询的sql语句
    private String creatSQL(Users users){
        StringBuffer stringBuffer=new StringBuffer("select * from users where 1=1");
        if (users.getUsersex()!=null && !users.getUsersex().isEmpty()){
            stringBuffer.append(" and usersex= "+users.getUsersex());
        }
        if (users.getUsername()!=null && !users.getUsername().isEmpty()){
            stringBuffer.append(" and username= "+users.getUsername());
        }
        if (users.getPhonenumber()!=null && !users.getPhonenumber().isEmpty()){
            stringBuffer.append(" and phonenumber= "+users.getPhonenumber());
        }
        if (users.getQqnumber()!=null && !users.getQqnumber().isEmpty()){
            stringBuffer.append(" and qqnumber= "+users.getQqnumber());
        }
        return stringBuffer.toString();
    }
}
